{
    "metadata": {
        "kernelspec": {
            "name": "python3",
            "display_name": "Python 3 (ipykernel)",
            "language": "python"
        },
        "language_info": {
            "name": "python",
            "version": "3.8.10",
            "mimetype": "text/x-python",
            "codemirror_mode": {
                "name": "ipython",
                "version": 3
            },
            "pygments_lexer": "ipython3",
            "nbconvert_exporter": "python",
            "file_extension": ".py"
        },
        "vscode": {
            "interpreter": {
                "hash": "878db934b706db2770cee331c11f15a67312cefb4f2334de757c7c9b6e34ef9f"
            }
        }
    },
    "nbformat_minor": 2,
    "nbformat": 4,
    "cells": [
        {
            "cell_type": "markdown",
            "source": [
                "![Microsoft](https://raw.githubusercontent.com/microsoft/azuredatastudio/main/extensions/resource-deployment/images/microsoft-small-logo.png)\n",
                "\n",
                "## Run SQL Server 2022 container image with Docker\n",
                "\n",
                "This notebook will use Docker to pull and run the SQL Server 2022 container image and connect to it in Azure Data Studio\n",
                "\n",
                "### Dependencies\n",
                "\n",
                "- Docker Engine. For more information, see [Install Docker](https://docs.docker.com/engine/installation/).\n",
                "\n",
                "<span style=\"color:red;\"><font size=\"3\">Please press the \"Run all\" button to run the notebook</font></span>"
            ],
            "metadata": {
                "azdata_cell_guid": "e5fb2be9-e904-4821-8473-b69b90760c6a"
            },
            "attachments": {}
        },
        {
            "cell_type": "markdown",
            "source": [
                "### Check dependencies"
            ],
            "metadata": {
                "azdata_cell_guid": "76c571ab-358a-4b07-810c-53020ee1745a"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "import sys,os,getpass,json,html,time\n",
                "from string import Template\n",
                "\n",
                "def run_command(displayCommand = \"\"):\n",
                "    print(\"Executing: \" + displayCommand if displayCommand != \"\" else cmd)\n",
                "    !{cmd}\n",
                "    if _exit_code != 0:\n",
                "        sys.exit(f'Command execution failed with exit code: {str(_exit_code)}.\\n')\n",
                "    print(f'Command successfully executed')\n",
                "\n",
                "cmd = 'docker version'\n",
                "run_command()"
            ],
            "metadata": {
                "azdata_cell_guid": "6196300e-f896-489b-8dca-b2c42eda2d6d",
                "tags": [
                    "hide_input"
                ],
                "language": "python"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "### List existing containers\n",
                "You can view the ports that have been used by existing containers"
            ],
            "metadata": {
                "azdata_cell_guid": "87b07614-d57d-4731-ac3e-a8b324d231f2"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "cmd = f'docker ps -a'\n",
                "run_command()"
            ],
            "metadata": {
                "azdata_cell_guid": "26170d1b-4332-4383-bcc4-1d97030daffc",
                "tags": [
                    "hide_input"
                ],
                "language": "python"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "### Required information"
            ],
            "metadata": {
                "azdata_cell_guid": "52b1faf2-d7c7-446b-ba0b-4f8b744da0bb"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "env_var_flag = \"AZDATA_NB_VAR_DOCKER_PASSWORD\" in os.environ\n",
                "password_name = 'SQL Server sa account password'\n",
                "if env_var_flag:\n",
                "    sql_password = os.environ[\"AZDATA_NB_VAR_DOCKER_PASSWORD\"]\n",
                "    sql_port = os.environ[\"AZDATA_NB_VAR_DOCKER_PORT\"]\n",
                "else:\n",
                "    sql_password = getpass.getpass(prompt = password_name)\n",
                "    password_confirm = getpass.getpass(prompt = f'Confirm {password_name}')\n",
                "    if sql_password != password_confirm:\n",
                "        raise SystemExit(f'{password_name} does not match the confirmation password.')\n",
                "    sql_port = input('SQL Server port, default value is 1433')\n",
                "    if len(sql_port) == 0:\n",
                "        sql_port = '1433'\n",
                "print(f'{password_name}: ******')\n",
                "print(f'Port: {sql_port}')"
            ],
            "metadata": {
                "azdata_cell_guid": "93cb0147-7bf6-4630-b796-3811dfd1354b",
                "tags": [
                    "hide_input"
                ],
                "language": "python"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "### Pull the container image"
            ],
            "metadata": {
                "azdata_cell_guid": "643ccaca-fd1d-4482-b81e-aee29b627e34"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "cmd = f'docker pull mcr.microsoft.com/mssql/server:2022-latest'\n",
                "run_command()"
            ],
            "metadata": {
                "azdata_cell_guid": "7b102447-3198-488f-a995-982ae1fc8555",
                "tags": [
                    "hide_input"
                ],
                "language": "python"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "### Start a new container"
            ],
            "metadata": {
                "azdata_cell_guid": "a4527a5f-c2c5-4f60-bfd1-b119576178c5"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "if env_var_flag:\n",
                "    container_name = os.environ[\"AZDATA_NB_VAR_DOCKER_CONTAINER_NAME\"]\n",
                "else:\n",
                "    container_name = 'sql2022-' + time.strftime(\"%Y%m%d%H%M%S\", time.localtime())\n",
                "print('New container name: ' + container_name)\n",
                "\n",
                "template = Template(f'docker run -e ACCEPT_EULA=Y -e \"SA_PASSWORD=$password\" -p {sql_port}:1433 --name {container_name} -d mcr.microsoft.com/mssql/server:2022-latest')\n",
                "cmd = template.substitute(password=sql_password)\n",
                "run_command(template.substitute(password='******'))"
            ],
            "metadata": {
                "azdata_cell_guid": "82f27460-88eb-4484-92ee-40305e650d70",
                "tags": [
                    "hide_input"
                ],
                "language": "python"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "### List all the containers"
            ],
            "metadata": {
                "azdata_cell_guid": "e267aa7d-dd22-43ac-9b03-cf282ef15f67"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "cmd = f'docker ps -a'\n",
                "run_command()"
            ],
            "metadata": {
                "azdata_cell_guid": "211ee198-f1d1-4781-9daa-8497c2665de6",
                "tags": [
                    "hide_input"
                ],
                "language": "python"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "### Connect to SQL Server in Azure Data Studio\n",
                "It might take a couple minutes for SQL Server to launch"
            ],
            "metadata": {
                "azdata_cell_guid": "5f5860c4-7962-439e-a15b-7f24f504dc18"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "from IPython.display import *\n",
                "connectionParameter = '{\"serverName\":\"localhost,' + sql_port + '\",\"providerName\":\"MSSQL\",\"authenticationType\":\"SqlLogin\",\"userName\":\"sa\",\"password\":' + json.dumps(sql_password) + ',\"options\": {\"trustServerCertificate\":true}}'\n",
                "display(HTML('<br/><a href=\"command:azdata.connect?' + html.escape(connectionParameter)+'\"><font size=\"3\">Click here to connect to SQL Server</font></a><br/>'))\n",
                "display(HTML('<br/><span style=\"color:red\"><font size=\"2\">NOTE: The SQL Server password is included in this link, you may want to clear the results of this code cell before saving the notebook.</font></span>'))"
            ],
            "metadata": {
                "azdata_cell_guid": "4bc64915-c5ae-4507-8fb0-9e413ccc2fd0",
                "tags": [
                    "hide_input"
                ],
                "language": "python"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "### Stop and remove the container"
            ],
            "metadata": {
                "azdata_cell_guid": "9a1039fa-fdd3-408b-b649-8fde0fcee660"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "stop_container_command = f'docker stop {container_name}'\n",
                "remove_container_command = f'docker rm {container_name}'\n",
                "display(HTML(\"Use this link to: <a href=\\\"command:workbench.action.terminal.focus\\\">open the terminal window in Azure Data Studio</a> and use the links below to paste the command to the terminal.\"))\n",
                "display(HTML(\"Stop the container: <a href=\\\"command:workbench.action.terminal.sendSequence?%7B%22text%22%3A%22\"+stop_container_command.replace(\" \",\"%20\")+\"%22%7D\\\">\" + stop_container_command + \"</a>\"))\n",
                "display(HTML(\"Remove the container: <a href=\\\"command:workbench.action.terminal.sendSequence?%7B%22text%22%3A%22\"+remove_container_command.replace(\" \",\"%20\")+\"%22%7D\\\">\" + remove_container_command + \"</a>\"))"
            ],
            "metadata": {
                "azdata_cell_guid": "f9e0f1ad-ba6e-4c17-84ea-cc5dceb1289b",
                "tags": [
                    "hide_input"
                ],
                "language": "python"
            },
            "outputs": [],
            "execution_count": null
        }
    ]
}